package gggameserver.dbaccess;

import gggameserver.GGGameServerGameResult;
import gggameserver.compiler.GGSolutionData;
import gggameserver.dbaccess.data.GGDBAGameInfo;
import gggameserver.exceptions.GGNoDataFoundException;
import gggameserver.util.GGConfiguration;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.sql.rowset.serial.SerialBlob;

import org.apache.log4j.Logger;

public class GGMySqlDBAccess implements GGDBAccess {

    private static org.apache.log4j.Logger log = Logger.getLogger(GGMySqlDBAccess.class);
	
	Connection sqlconnection;

	public GGMySqlDBAccess() throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		initConnection();
	}
	
	@Override
	public GGDBAGameInfo getNewGame() {
		Connection con = null;
		
		try {
			con = this.getConnection();
			return getNewGameCore(con);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	@Override
	public GGSolutionData getSolutionData(long id) {
		Connection con = null;
		
		try {
			con = this.getConnection();
			return new GGSolutionData(con, id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (GGNoDataFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}

	@Override
	public GGSolutionData getSolutionDataByProgramId(long prog_id) {
		Connection con = null;
		
		try {
			con = this.getConnection();
			return new GGSolutionData(con, prog_id, true);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (GGNoDataFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return null;
	}
	
	@Override
	public boolean submitGameResults(long p_game_id, long prog_1, long prog_2,
			Long prog_winner, GGGameServerGameResult ladderGame) 
	{
		
		Connection con;
		CallableStatement game_res = null;
		try {
			con = this.getConnection();
			Blob logData = new SerialBlob(ladderGame.getGameLogData());
			game_res = con.prepareCall("{call p_post_ladder_game_results(?, ?, ?, ?, ?, ?, ?)}");
			game_res.setLong(1, p_game_id); //IN p_ladder_game_id BIGINT,
			game_res.setLong(2, prog_1); //IN p_program1_id BIGINT,
			game_res.setLong(3, prog_2); //IN p_program2_id BIGINT,
			
			if (null != prog_winner) game_res.setLong(4, prog_winner); //IN p_winner_id BIGINT,
			else game_res.setNull(4, java.sql.Types.INTEGER);
			
			game_res.setTimestamp(5, ladderGame.getStartTime()); //IN p_starttime DATETIME,
			game_res.setTimestamp(6, ladderGame.getEndTime()); //IN p_endtime DATETIME,
			game_res.setBlob(7, logData); //IN p_game_log TEXT
			
			return game_res.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (null != game_res)
				try {
					game_res.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		
		return false;
	}

	@Override
	public boolean setCompileResult(long prog_id, char result, String log) {
		Connection con = null;
		try {
			con = this.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		PreparedStatement stmt = null;
		try {
			stmt = con.prepareStatement(
					"update programs set compile_result = ?, compile_log = ? where id = ?");
			stmt.setString(1, "" + result);
			stmt.setString(2, log);
			stmt.setLong(3, prog_id);
			
			if (!stmt.execute()) {
				//con.rollback();
				return false;
			}
			
			//con.commit();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		} finally {
			if (null != stmt)
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
	}

	private GGDBAGameInfo getNewGameCore(Connection con) throws ClassNotFoundException, SQLException {
		// ladder result
		PreparedStatement ladder = con.prepareStatement("select f_generate_ladder_round_if_needed() from dual");
		ResultSet ladder_res = ladder.executeQuery();
		if (!ladder_res.next()) {
			log.warn("Failed to update ladder");
			return null;
		}
		long ladder_result = ladder_res.getLong(1);
		ladder_res.close();
		log.debug("Ladder result = " + ladder_result);
		
		//if (0 == ladder_result) return false;

		// p_start_ladder_game
		CallableStatement game_info = con.prepareCall("{call p_start_ladder_game(?, ?, ?)}");
		game_info.registerOutParameter(1, java.sql.Types.BIGINT);
		game_info.registerOutParameter(2, java.sql.Types.BIGINT);
		game_info.registerOutParameter(3, java.sql.Types.BIGINT);
		
		if (game_info.execute()) {
			log.warn("Can't get game info");
			return null;
		}
		
		long p_game_id = game_info.getLong(1);
		boolean success = !game_info.wasNull();
		long p_solution1_id = game_info.getLong(2);
		long p_solution2_id = game_info.getLong(3);
		
		game_info.close();
		if (!success) return null;
		
		return new GGDBAGameInfo(p_game_id, p_solution1_id, p_solution2_id);
	}
	
	@Override
	public void deleteAvailableGame(long p_game_id) {
		PreparedStatement stmt;
		try {
			GGMySqlDBAccess access = GGMySqlDBAccess.getInstance();
			Connection con = access.getConnection();
			stmt = con.prepareStatement("delete from available_ladder_games where id = ?");
			stmt.setLong(1, p_game_id);
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	private void initConnection() throws SQLException {
		log.info("Establishing DB connection...");
		sqlconnection = DriverManager.getConnection(
				GGConfiguration.getProperty("db.connection"),
				GGConfiguration.getProperty("db.user"),
				GGConfiguration.getProperty("db.password")
				);
	}
	
	public Connection getConnection() throws SQLException {
		if (!sqlconnection.isValid(3000)) {
			log.info("Connection dropped...");
			initConnection();
		}
		return sqlconnection;
	}
	
	static GGMySqlDBAccess instance = null;
	public static synchronized GGMySqlDBAccess getInstance() throws ClassNotFoundException, SQLException {
		if (null == instance) instance = new GGMySqlDBAccess();
		return instance;
	}

}
